﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Mail;
using System.Net;
using System.Web.Mvc;
using System.IO;
using System.Text;
using System.Xml.Linq;
using System.Xml;

namespace BadmintonBuddy.Models
{
    public class ClubRepository
    {
        private ClubDataContext db = new ClubDataContext();
        public IQueryable<Club> GetAllClubs(string param)
        {
            IQueryable<Club> clubs;
            try
            {
                switch (param)
                {
                    case "City":
                        clubs = from club in db.Clubs
                                where club.IsApproved != false
                                orderby club.City.CityName ascending
                                select club;
                        break;
                    case "Name":
                    default:
                        clubs = from club in db.Clubs
                                where club.IsApproved != false
                                orderby club.ClubName ascending
                                select club;
                        break;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return clubs;
        }

        public List<String> GetCountry(string query)
        {
            IQueryable<string> countries;
            try
            {
                if (String.IsNullOrEmpty(query))
                {
                    countries = from country in db.Countries
                                select country.CountryName;
                }
                else
                {
                    countries = from country in db.Countries
                                where country.CountryName.StartsWith(query)
                                select country.CountryName;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return countries.ToList();
        }

        public void Add(Club club)
        {
            db.Clubs.InsertOnSubmit(club);
        }

        public int Save(FormCollection clubDetails,bool isApproved)
        {
            try
            {
                Club club = new Club();
                club.ClubName = clubDetails["ClubName"];
                club.Description = clubDetails["Description"];
                club.Address = clubDetails["Address"];
                club.Email = clubDetails["Email"];
                club.Fees = clubDetails["Fees"];
                club.Timings = clubDetails["Timings"];
                float latitude;
                float longitude;
                if (!String.IsNullOrEmpty(clubDetails["Latitude"]) && float.TryParse(clubDetails["Latitude"], out latitude))
                {
                    club.Latitude = latitude;
                }

                if (!String.IsNullOrEmpty(clubDetails["Longitude"]) && float.TryParse(clubDetails["Longitude"], out longitude))
                {
                    club.Longitude = longitude;
                }
                
                club.PhoneNumber = clubDetails["PhoneNumber"];
                club.Website = clubDetails["Website"]; 
                club.Surface = int.Parse(clubDetails["Surface"]);
                club.Courts = int.Parse(clubDetails["Courts"]);
                club.Added = DateTime.Now;                
                club.IsApproved = isApproved;

                if (!String.IsNullOrEmpty(clubDetails["City.CityName"]))
                {
                    foreach (City city in db.Cities)
                    {
                        if (city.CityName.ToLower().Equals(clubDetails["City.CityName"].ToLower()))
                        {
                            club.City = city;
                            break;
                        }
                    }

                    if (club.City == null)
                    {
                        City newCity = new City();
                        newCity.CityName = clubDetails["City.CityName"];
                        club.City = newCity;                        
                    }
                }
                else
                {
                    club.City = db.Cities.First();    //assign the first city in the record and then re-modify.
                }



                if (!String.IsNullOrEmpty(clubDetails["City.State.StateName"]))
                {
                    foreach (State state in db.States)
                    {
                        if (state.StateName.ToLower().Equals(clubDetails["City.State.StateName"].ToLower()))
                        {
                            club.City.State = state;
                            break;
                        }
                    }
                    if (club.City.State == null)
                    {
                        State newState = new State();
                        newState.StateName = clubDetails["City.State.StateName"];
                        club.City.State = newState;

                    }
                }
                else
                {
                    club.City.State = db.States.First();    //assign the first state in the record and then re-modify.
                }

                if (!String.IsNullOrEmpty(clubDetails["City.State.Country.CountryName"]))
                {
                    foreach (Country country in db.Countries)
                    {
                        if (country.CountryName.ToLower().Equals(clubDetails["City.State.Country.CountryName"].ToLower()))
                        {
                            club.City.State.Country = country;
                            break;
                        }
                    }

                    if (club.City.State.Country == null)
                    {
                        Country newCountry = new Country();
                        newCountry.CountryName = clubDetails["City.State.Country.CountryName"];
                        club.City.State.Country = newCountry;
                    }
                }
                else
                {
                    club.City.State.Country = db.Countries.First();    //assign the first country in the record and then re-modify.
                }
                
                db.Clubs.InsertOnSubmit(club);
                db.SubmitChanges();

                return club.ClubID;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Log(int typeId, string log)
        {
            LogDetail detail = new LogDetail();
            detail.TypeID = typeId;
            detail.LogDetails = log;
            db.LogDetails.InsertOnSubmit(detail);
            db.SubmitChanges();
        }

        public List<String> GetState(string query,string country)
        {
            IQueryable<String> states;
            try
            {
                if (string.IsNullOrEmpty(country))
                {
                    states = from state in db.States
                             where state.StateName.StartsWith(query)
                             select state.StateName;
                }
                else
                {
                    states = from state in db.States
                             join countries in db.Countries
                             on state.CountryID equals countries.CountryID
                             where countries.CountryName.Equals(country) && state.StateName.StartsWith(query)
                             select state.StateName;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return states.ToList();
        }

        public IEnumerable<SelectListItem> GetAllStates()
        {
            try
            {
                IEnumerable<SelectListItem> stateNames = from states in db.States
                                                         select new SelectListItem
                                                         {
                                                             Text = states.StateName,
                                                             Value = states.StateID.ToString()
                                                         };
                return stateNames;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<SelectListItem> GetAllCountries()
        {
            try
            {
                IEnumerable<SelectListItem> countryNames = from countries in db.Countries
                                                           select new SelectListItem
                                                           {
                                                               Text = countries.CountryName,
                                                               Value = countries.CountryID.ToString()
                                                           };
                return countryNames;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<SelectListItem> GetAllCities()
        {
            try
            {
                IEnumerable<SelectListItem> citiNames = from cities in db.Cities
                                                        select new SelectListItem
                                                        {
                                                            Text = cities.CityName,
                                                            Value = cities.CityID.ToString()
                                                        };
                return citiNames;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public List<String> GetCity(string query,string state)
        {
            try
            {
                IQueryable<String> cities;
                if (String.IsNullOrEmpty(state))
                {

                    cities = from city in db.Cities
                             where city.CityName.StartsWith(query)
                             select city.CityName;
                }
                else
                {
                    cities = from city in db.Cities
                             join states in db.States
                             on city.StateID equals states.StateID
                             where states.StateName.Equals(state) && city.CityName.StartsWith(query)
                             select city.CityName;
                }
                return cities.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IQueryable<Club> GetClubsByRegion(Region region)
        {
            try
            {
                var clubs = from club in db.Clubs
                            where (club.Latitude < region.EastLatitude && club.Latitude > region.WestLatitude)
                            && (club.Longitude < region.EastLongitude && club.Longitude > region.WestLongitude)
                            && club.IsApproved != false
                            select club;
                return clubs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IQueryable<Club> GetClubsByLocation(string location)
        {
            try
            {
                var clubs = from club in db.Clubs
                            where club.City.CityName.ToLower().Contains(location.ToLower()) && club.IsApproved != false
                            select club;
                return clubs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public IQueryable<Club> GetClubsByLocation(string location,int excludeId)
        {
            try
            {
                var clubs = from club in db.Clubs
                            where (club.City.CityName.ToLower().Contains(location.ToLower())) && (club.ClubID != excludeId) && club.IsApproved != false
                            select club;
                return clubs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IQueryable<AutoSuggest> AutoSuggest(string searchtext)
        {
            try
            {
                //To DO: See how we can move the "ClubName" and "Location" from DataLayer
                //to View.
                var clubs = (from club in db.Clubs
                             where club.ClubName.Contains(searchtext)
                             select new AutoSuggest { Value = club.ClubName, Type = "Name" }).Union
                             (from city in db.Cities
                              where city.CityName.Contains(searchtext)
                              select new AutoSuggest { Value = city.CityName, Type = "Location" });

                return clubs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Club SearchClubById(int clubId)
        {
            try
            {
                var selected_club = from club in db.Clubs
                                    where club.ClubID == clubId
                                    select club;
                return selected_club.First<Club>();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public IQueryable<Club> SearchClub(string searchtext,string filter)
        {
            try
            {
                //TO DO: Create queries as per the requirement for City and NoFilter.
                searchtext = searchtext.Trim().ToLower();
                switch (filter)
                {
                    case "0":     //For search by Location, have a equals search as user selects from the autosuggest
                        var clubs = from club in db.Clubs
                                    join city in db.Cities
                                    on club.CityID equals city.CityID
                                    where city.CityName.Trim().ToLower().Equals(searchtext) && club.IsApproved != false
                                    orderby club.ClubName
                                    select club;
                        return clubs;
                    case "1":     //For search by Clubname
                        var clubByName = from club in db.Clubs
                                         where club.ClubName.Contains(searchtext) && club.IsApproved != false
                                         select club;
                        return clubByName;
                    case "-1":    // For no filter/default.
                    default:
                        var clubNoFilter = from club in db.Clubs
                                           join city in db.Cities
                                           on club.CityID equals city.CityID
                                           where (city.CityName.Trim().ToLower().Contains(searchtext) || club.ClubName.Trim().ToLower().Contains(searchtext)) && club.IsApproved != false
                                           orderby club.ClubName
                                           select club;

                        return clubNoFilter;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        public void Add(FormCollection collection)
        {
            try
            {
                string text = "Name:" + collection.Get("ClubName") +
                              "Country:" + collection.Get("City.State.Country.CountryName") +
                              "State:" + collection.Get("City.State.StateName") +
                              "City:" + collection.Get("City.CityName") +
                              "Address:" + collection.Get("Address") +
                              "Phone:" + collection.Get("PhoneNumber") +
                              "Website:" + collection.Get("Website") +
                              "Email:" + collection.Get("Email") +
                              "Fees:" + collection.Get("Fees") +
                              "Timings:" + collection.Get("Timings") +
                              "Description:" + collection.Get("Description") +
                              "Courts:" + collection.Get("Courts") +
                              "Others:" + collection.Get("Others") +
                              "Surface:" + collection.Get("Surface") +
                              "Owner:" + collection.Get("Owner");                
                
            }
            catch (Exception ex)
            {
                string exception = ex.Message;
            }

        }

       
       
        public IQueryable<Club> GetClubDetails(string clubname)
        {
            try
            {
                var clubDetails = from club in db.Clubs
                                  where club.ClubName.ToLower().Contains(clubname.ToLower())
                                  select club;
                return clubDetails;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        internal void Save()
        {
            try
            {   
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        internal IQueryable<LogDetail> GetLogs()
        {
            IQueryable<LogDetail> logDetails = from details in db.LogDetails
                                               select details;
            return logDetails;
        }

        internal IQueryable<Club> GetAllNonApprovedClubs()
        {
            return from clubs in db.Clubs
                   where clubs.IsApproved == false
                   select clubs;
            
        }

        internal IQueryable<User> GetUser(string userId)
        {
            IQueryable<User> user = from users in db.Users
                   where users.UserID == userId
                   select users;


            return user;
        }


        internal void InsertUser(User user)
        {
            try
            {
                db.Users.InsertOnSubmit(user);
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
    }

    public class Region
    {
        public float WestLatitude{get;set;}
        public float WestLongitude{get;set;}
        public float EastLatitude{get;set;}
        public float EastLongitude{get;set;}

    }
}
